#!/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql


dbprefix="tank30_"
dbtype=1 

class TankDb():
    def __init__(self,db_name, db_host, db_user_name, db_user_pwd, db_port, db_charset) -> None:
        self.db_name = db_name
        self.db_host = db_host
        self.db_user_name = db_user_name
        self.db_user_pwd = db_user_pwd
        self.db_port = db_port
        self.db_charset = db_charset
        cx = pymysql.connect(host=db_host, user=db_user_name, password=db_user_pwd, database=db_name, charset=db_charset, port=db_port)
        return
    

    def get_cx(self):
        cx = pymysql.connect(host=self.db_host, user=self.db_user_name, password=self.db_user_pwd, database=self.db_name, charset=self.db_charset, port=self.db_port)
        return cx

    def put_cx(self, cx):
        cx.close()
        return 

    # execute sql
    def execute_sql(self, sql, args=None):
        cx = self.get_cx()
        cu = cx.cursor()
        try:
            ret = cu.execute(sql, args)
            cx.commit()
        except Exception as e:
            print(sql)
            raise e
            return False, 0
        finally:
            cu.close()
            self.put_cx(cx)
            return False, 0
        return True, ret

    # execute sql
    def execute_fetchall(self, sql, args=None):
        cx = self.get_cx()
        cu = cx.cursor()
        record_set = []
        if cu.execute(sql, args):
            record_set = cu.fetchall()
        cu.close()
        self.put_cx(cx)
        return record_set

    # execute sql
    def execute_fetchone(self, sql, args=None):
        cx = self.get_cx()
        cu = cx.cursor()
        record_set = []
        if cu.execute(sql, args):
            record_set = cu.fetchone()
        cu.close()
        self.put_cx(cx)
        return record_set

    def search_user_uuid(self, user_name):
        sql = "select uuid from "+dbprefix+"user where username='"+user_name+"';"
        ret = self.execute_fetchone(sql)
        if ret:
            return ret[0]
        return ret

    def search_uuid(self, path):
        sql = "select uuid from "+dbprefix+"matter where path='"+path+"';"
        ret = self.execute_fetchone(sql)
        if ret:
            return ret[0]
        return ret

    #  INSERT into tank30_matter(uuid, user_uuid, username, puuid, dir , name, path, sort )
    #  values('1','43272d60-9150-42b7-7dc1-5e612c02727d','admin','root',0, 'abc', '/abc',1234567890123);
    def insert_matter(self, uuid, user_uuid, username, puuid, dir , name, path, sort, size=0):
        #sql = "INSERT into " + dbprefix + "matter(uuid, user_uuid, username, puuid, dir, name, path, sort, privacy,size) values('{}','{}','{}','{}',{}, '{}', '{}',{},1,{});".format(
        #    uuid, user_uuid, username, puuid, dir , name, path, sort, size
        #)
        sql = "INSERT into " + dbprefix + "matter(uuid, user_uuid, username, puuid, dir, name, path, sort, privacy,size) select '{}','{}','{}','{}',{}, '{}', '{}',{},1,{} from dual WHERE NOT EXISTS ( select path from `{}matter` where `path`='{}')".format(
            uuid, user_uuid, username, puuid, dir , name, path, sort, size, dbprefix, path
        )
        print (sql)
        ret = self.execute_sql(sql)
        return ret 

    #  INSERT into tank30_matter(uuid, user_uuid, username, puuid, dir , name, path, sort )
    #  values('1','43272d60-9150-42b7-7dc1-5e612c02727d','admin','root',0, 'abc', '/abc',1234567890123);
    def insert_matter_moved_to(self, uuid, user_uuid, username, puuid, dir , name, path, sort, size=0):
        sql = "INSERT into " + dbprefix + "matter(uuid, user_uuid, username, puuid, dir, name, path, sort, privacy,size) values('{}','{}','{}','{}',{}, '{}', '{}',{},1,{});".format(
            uuid, user_uuid, username, puuid, dir , name, path, sort, size
        )
        #sql = "INSERT into " + dbprefix + "matter(uuid, user_uuid, username, puuid, dir, name, path, sort, privacy,size) select '{}','{}','{}','{}',{}, '{}', '{}',{},1,{} from dual WHERE NOT EXISTS ( select path from `" + dbprefix + "matter` where `path`='{}')".format(
        #    uuid, user_uuid, username, puuid, dir , name, path, sort, size, path
        #)
        print (sql)
        ret = self.execute_sql(sql)
        return ret 
    
    def delete_matter(self, path):
        sql = "delete from " +dbprefix+ "matter where path='{}'".format(path)
        print(sql)
        ret = self.execute_sql(sql)
        return ret

    def delete_matter_like(self, path):
        sql = "delete from " +dbprefix+ "matter where path like '{}%'".format(path)
        print(sql)
        ret = self.execute_sql(sql)
        return ret